package p02_statement;

import Utils.DateTimeUtils;
import Utils.delegations.IFunc;
import Utils.converters.Data2SqlDataType;
import Utils.converters.String2Int;
import Utils.converters.String2String;
import Utils.db.JdbcUtilsStatic;
import Utils.excel.PoiUtils;
import bean.Customer;
import bean.Order;
import bean.Student;
import org.junit.jupiter.api.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;
import java.util.Scanner;

public class preparedStatement {
    @Test
    public void testInsert() throws IOException, ClassNotFoundException, SQLException, ParseException {
        InputStream is = preparedStatement.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties props = new Properties();
        props.load(is);
        String user = props.getProperty("user");
        String password = props.getProperty("password");
        String url = props.getProperty("url");
        String driverClassName = props.getProperty("driverClassName");

        Class.forName(driverClassName);
        Connection conn = DriverManager.getConnection(url, user, password);
//        System.out.println(conn);
        String sql = "insert into customers (name, email, birth) values (?,?,?);";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, "哪吒");
        ps.setString(2, "nezha@163.com");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = sdf.parse("1000-01-01");
        ps.setDate(3, new java.sql.Date(date.getTime()));
        ps.execute();
        ps.close();
        conn.close();
    }

    @Test
    public void testUpdate1() throws SQLException, ClassNotFoundException, IOException {
        String sql = "update customers set name= ? where id = ?;";
        Connection conn = JdbcUtilsStatic.getConnection();
        System.out.println(conn);
        JdbcUtilsStatic.modify(sql, conn, "哪吒3", 19);
        JdbcUtilsStatic.close(conn);
    }

    @Test
    public void testUpdate2() throws SQLException, ClassNotFoundException, IOException {
//        String sql = "update customers set name= ? where id = ?;";
        String sql = "update `order` set order_name= ? where order_id =?";
        JdbcUtilsStatic.modify(sql, "DD", 4);
    }

    @Test
    public void testSelectCustomer() {
        String sql = "select id, name, email, birth from customers where id > ?";
        ArrayList<Customer> customers = JdbcUtilsStatic.query(Customer.class, sql, 10);
        for (Customer customer : customers) {
            System.out.println(customer);
        }
    }

    @Test
    public void testSelectOrder() {
        String sql = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id > ?";
        ArrayList<Order> orders = JdbcUtilsStatic.query(Order.class, sql, 1);
        for (Order order : orders) {
            System.out.println(order);
        }
    }

    @Test
    public void testSelect1() {
        String sql = "select FlowID flowID, Type type, IDCard idCard, ExamCard examCard, StudentName studentName, Location location, Grade grade from examstudent where ExamCard = ?";
        ArrayList<Student> students = JdbcUtilsStatic.query(Student.class, sql, "123");
        for (Student student : students) {
            System.out.println(student);
        }
    }

    @Test
    public void testInsert1() throws SQLException {
        String sql = "insert into customers (`id`,`name`,`email`,`birth`) values (?,?,?,?)";

        JdbcUtilsStatic.modify(
                sql,
                21,
                "石豆豆1",
                "d11yushi@163.com",
                DateTimeUtils.utilDate2SqlDate(DateTimeUtils.getNow()));
    }

    @Test
    public void testInsert2() throws IOException {
        long start = System.currentTimeMillis();
        IFunc[] converters = new IFunc[7];
        String2Int string2Int = new String2Int();
        String2String string2String = new String2String();
        converters[0] = string2Int;
        converters[1] = string2Int;
        converters[2] = string2String;
        converters[3] = string2String;
        converters[4] = string2String;
        converters[5] = string2String;
        converters[6] = string2Int;

        ArrayList<Student> students = PoiUtils.getEntryList(Student.class,
                "D:\\个人文件\\IT\\Java\\练习\\JDBC\\examstudent.xlsx",
                new String[]{"flowID", "type", "idCard", "examCard", "studentName", "location", "grade"},
                converters
        );
        String sql = "insert into examstudent (FlowID, Type, IDCard, ExamCard, StudentName, Location, Grade) values (?, ?, ?, ?, ?, ?, ?)";
        JdbcUtilsStatic.modifyPerContent(
                sql,
                new Data2SqlDataType(),
                students,
                new String[]{"flowID", "type", "idCard", "examCard", "studentName", "location", "grade"});
//        for (Student student : students) {
//            System.out.println(student);
//        }
        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end - start));

    }

    @Test
    public void test05() throws SQLException {
        Scanner scanner = new Scanner(System.in);
        boolean flag = true;
        char key = ' ';
        while (flag) {
            System.out.print("请输入指令(i：新增；s：查询；d：删除；e：退出)：");
            key = scanner.next().charAt(0);
            switch (key) {
                case 'i': {
                    System.out.println("请输入以下信息：");
                    System.out.print("四级/六级（4/6）：");
                    int type = Integer.parseInt(scanner.next());
                    System.out.print("身份证号码：");
                    String idCard = scanner.next();
                    System.out.print("准考证号码：");
                    String examCard = scanner.next();
                    System.out.print("学生姓名：");
                    String studentName = scanner.next();
                    System.out.print("区域：");
                    String location = scanner.next();
                    System.out.print("成绩：");
                    String grade = scanner.next();

                    String sql = "insert into examstudent (Type, IDCard, ExamCard, StudentName, Location, Grade) values (?, ?, ?, ?, ?, ?);";
                    JdbcUtilsStatic.modify(sql, type, idCard, examCard, studentName, location, grade);
                    break;
                }
                case 's': {
                    System.out.print("请选择要输入的类型：(a：准考证号；b：身份证号)");
                    char inputOption = scanner.next().charAt(0);
                    Student student = null;
                    if (inputOption == 'a') {
                        System.out.print("准考证号：");
                        String examCard = scanner.next();
                        String sql = "select FlowID flowID, Type type, IDCard idCard, ExamCard examCard, StudentName studentName, Location location, Grade grade from examstudent where ExamCard = ?";
                        student = JdbcUtilsStatic.query(Student.class, sql, examCard).get(0);
                    } else if (inputOption == 'b') {
                        System.out.print("身份证号：");
                        String idCard = scanner.next();
                        String sql = "select FlowID flowID, Type type, IDCard idCard, ExamCard examCard, StudentName studentName, Location location, Grade grade from examstudent where IDCard = ?";
                        student = JdbcUtilsStatic.query(Student.class, sql, idCard).get(0);
                    }
                    System.out.println("========查询结果=========");
                    System.out.println("流水号：" + student.getFlowID());
                    System.out.println("四级/六级：" + student.getType());
                    System.out.println("身份证号：" + student.getIdCard());
                    System.out.println("准考证号：" + student.getExamCard());
                    System.out.println("学生姓名：" + student.getStudentName());
                    System.out.println("区域：" + student.getLocation());
                    System.out.println("成绩：" + student.getGrade());
                    break;
                }
                case 'd': {
                    System.out.print("请选择要输入的类型：(a：准考证号；b：身份证号)");
                    char inputOption = scanner.next().charAt(0);
                    if (inputOption == 'a') {
                        System.out.print("准考证号：");
                        String examCard = scanner.next();
                        String sql = "delete from examstudent where ExamCard = ?";
                        JdbcUtilsStatic.modify(sql, examCard);
                    } else if (inputOption == 'b') {
                        System.out.print("身份证号：");
                        String idCard = scanner.next();
                        String sql = "delete from examstudent where IDCard = ?";
                        JdbcUtilsStatic.modify(sql, idCard);
                    }
                    System.out.println("删除成功！");
                    break;
                }
                case 'e':
                    flag = false;
                    break;
                default:
                    break;
            }
            System.out.println("=======================");
        }
    }
}
